#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import pymysql
import csv
import datetime
import mysql.connector


__author__ = 'hubert'


host = '192.168.4.70'
user = 'canal'
password = 'Canal@123456'
db_name = 'vas_guizhou'


# 写入csv文件
def write_csv(f, name, description):
    # 1. 创建文件对象
    # f = open(path,'w',encoding='utf-8')
    # 2. 基于文件对象构建 csv写入对象
    csv_writer = csv.writer(f)
    # 3. 构建列表头
    # csv_writer.writerow(["名称","code"])
    # 4. 写入csv文件内容
    csv_writer.writerow([name, description])
    # 5. 关闭文件
    # f.close()


def checkindex(f):
    # 建立连接
    conn = pymysql.connect(host=host, user=user, password=password, db=db_name, charset="utf8")

    # 游标
    cursor = conn.cursor()
    try:
        sql = 'show tables'
        # 执行SQL语句
        cursor.execute(sql)

        # 获取所有记录列表
        listTable = cursor.fetchall()

        for table in listTable:
            # 转为字符串
            str_table = ''.join(table)
            #    print(str_table)
            sql2 = "show index from " + str_table

            cursor.execute(sql2)
            listindex = cursor.fetchall()

            sumIndex = len(listindex)

            if sumIndex == 0:
                print(str_table, '：无主键')
                write_csv(f, str_table, "无主键")
            elif sumIndex == 1:
                print(str_table,  '：仅有主键')
                write_csv(f, str_table, "仅有主键")
            else:
                for key in listindex:
                    print(key[0], ":", key[2], " ", key[4])
    except:
        print("Error: unable to fetch data")
    cursor.close()
    conn.close()

#    if not listIndex:
#        print('无主键')
#    for index in listIndex:
#        if index is None:
#            print('主键也没有')
#        print(len(index))

# 运行
if __name__ == '__main__':
    # 当前目录
    csvPath = "index"+datetime.datetime.now().strftime('%Y-%m-%d-%H') +".csv"
    # 1.创建文件对象
    f = open(csvPath, 'w', encoding='utf-8-sig')
    # 2.基于文件对象构建 csv写入对象
    csv_writer = csv.writer(f)
    # 3. 构建列表头
    csv_writer.writerow(["表名称", "描述"])
    # 4. 写入csv文件内容
    checkindex(f)
    # 5. 关闭文件
    f.close()


